﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using System.Linq;
using Dapper;
using BCCommon.Extensions;
using BCCommon.FreightEnums;
using BCEntity.Common.Area;
using BCCommon;

namespace BCData.Common.Area
{
    public class ProvinceData : IProvinceData
    {
        private static DateTime? LastLoadOrCheckTime = null;

        private static IEnumerable<ProvinceEntity> CachedRawData;
        private static IDictionary<string, ProvinceEntity> CachedByProvinceId;

        protected readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT ProvinceId,ProvinceNegotiatedPricePartLoad,Longitude,Latitude,IsEnabled,ProvinceCode,ProvinceName,ProvinceHeatLevel,ProvinceEasyBackGoods,
                                            CreateTime,UpdateTime,Editer,ProvinceNegotiatedPrice 
                                        FROM province";
        public ProvinceData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        private void EnsureCache()
        {
            bool reload = false;

            if (LastLoadOrCheckTime == null)
            {
                reload = true;
            }

            if (LastLoadOrCheckTime < DateTime.Now.AddSeconds(-293))
            {
                var lastUpdateTime = this.databaseContext.Slave.ExecuteScalar<DateTime>("SELECT UpdateTime FROM province ORDER BY UpdateTime DESC LIMIT 1");

                if (lastUpdateTime > LastLoadOrCheckTime)
                {
                    reload = true;
                }
                else
                {
                    LastLoadOrCheckTime = DateTime.Now;
                }
            }

            if (reload)
            {
                LastLoadOrCheckTime = DateTime.Now;

                CachedRawData = this.databaseContext.Slave.Query<ProvinceEntity>("SELECT * FROM province");

                CachedByProvinceId = new Dictionary<string, ProvinceEntity>();

                foreach (var item in CachedRawData)
                {
                    CachedByProvinceId.TryAdd(item.ProvinceCode, item);
                }
            }
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="entity"></param>
        public bool Add(ProvinceEntity entity)
        {
            LastLoadOrCheckTime = null;

            string sql = @"INSERT INTO Province(ProvinceCode,ProvinceName,ProvinceHeatLevel,ProvinceEasyBackGoods,Editer,ProvinceNegotiatedPrice,ProvinceNegotiatedPricePartLoad,Longitude,Latitude,IsEnabled)
                           VALUES(?ProvinceCode,?ProvinceName,?ProvinceHeatLevel,?ProvinceEasyBackGoods,?Editer,?ProvinceNegotiatedPricePartLoad,?ProvinceNegotiatedPricePartLoad,?Longitude,?Latitude,?IsEnabled);";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceCode", entity.ProvinceCode, DbType.AnsiString, size: 20);
            parameter.Add("?ProvinceName", entity.ProvinceName, DbType.AnsiString, size: 50);
            parameter.Add("?ProvinceHeatLevel", entity.ProvinceHeatLevel, DbType.Int32, size: 11);
            parameter.Add("?ProvinceEasyBackGoods", entity.ProvinceEasyBackGoods.ToString(), DbType.AnsiString);
            parameter.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);
            parameter.Add("?ProvinceNegotiatedPrice", entity.ProvinceNegotiatedPrice, DbType.Boolean);
            parameter.Add("?ProvinceNegotiatedPricePartLoad", entity.ProvinceNegotiatedPricePartLoad, DbType.Boolean);
            parameter.Add("?Longitude", entity.Longitude.Value, DbType.Double);
            parameter.Add("?Latitude", entity.Latitude.Value, DbType.Double);
            parameter.Add("?IsEnabled", entity.IsEnabled, DbType.Boolean);

            int result = this.databaseContext.Master.Execute(sql, parameter, databaseContext.Transaction);
            return result > 0;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        public bool Update(ProvinceEntity entity)
        {
            LastLoadOrCheckTime = null;

            string sql = @"UPDATE Province SET 
                                ProvinceName=?ProvinceName,
                                ProvinceHeatLevel=?ProvinceHeatLevel,
                                ProvinceEasyBackGoods=?ProvinceEasyBackGoods,
                                Editer=?Editer,
                                ProvinceNegotiatedPrice=?ProvinceNegotiatedPrice,
                                ProvinceNegotiatedPricePartLoad=?ProvinceNegotiatedPricePartLoad
                                WHERE ProvinceCode=?ProvinceCode";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceCode", entity.ProvinceCode, DbType.AnsiString, size: 20);
            parameter.Add("?ProvinceName", entity.ProvinceName, DbType.AnsiString, size: 50);
            parameter.Add("?ProvinceHeatLevel", entity.ProvinceHeatLevel, DbType.Int32, size: 11);
            parameter.Add("?ProvinceEasyBackGoods", entity.ProvinceEasyBackGoods.ToString(), DbType.AnsiString);
            parameter.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);
            parameter.Add("?ProvinceNegotiatedPrice", entity.ProvinceNegotiatedPrice, DbType.Boolean);
            parameter.Add("?ProvinceNegotiatedPricePartLoad", entity.ProvinceNegotiatedPricePartLoad, DbType.Boolean);

            var result = this.databaseContext.Master.Execute(sql, parameter, databaseContext.Transaction);
            return result != 0;
        }

        public bool Delete(string provinceCode)
        {
            LastLoadOrCheckTime = null;

            string sql = @"DELETE FROM Province WHERE ProvinceCode=?ProvinceCode";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            var i = this.databaseContext.Master.Execute(sql, parameter, databaseContext.Transaction);
            return i > 0;
        }
        /// <summary>
        /// 获取省份
        /// </summary>
        /// <param name="provinceCode">省份唯一ID</param>
        /// <returns></returns>
        public ProvinceEntity GetProvince(long provinceId)
        {
            string sql = string.Format("{0} WHERE ProvinceId=?ProvinceId", QUERY);

            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceId", provinceId, DbType.Int64, size: 20);

            return this.databaseContext.Slave.QueryEntity<ProvinceEntity>(sql, parameter);

        }

        public ProvinceEntity GetProvince(string provinceCode)
        {
            this.EnsureCache();

            if (CachedByProvinceId.TryGetValue(provinceCode, out var value))
            {
                return value;
            }
            else
            {
                string sql = @"SELECT ProvinceId,ProvinceCode,ProvinceName,ProvinceHeatLevel,ProvinceEasyBackGoods,CreateTime,UpdateTime,Editer,ProvinceNegotiatedPrice,ProvinceNegotiatedPricePartLoad
                            FROM Province WHERE ProvinceCode=?ProvinceCode";

                DynamicParameters parameter = new DynamicParameters();
                parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);

                return this.databaseContext.Slave.QueryEntity<ProvinceEntity>(sql, parameter);
            }
        }

        public IEnumerable<ProvinceEntity> GetProvinces()
        {
            this.EnsureCache();

            return CachedRawData;
        }

        public IEnumerable<ProvinceEntity> GetProvinces(string provinceCode, int index, int size, out int dataCount)
        {
            index = index == 0 ? 1 : index;
            int startIndex = (index - 1) * size;
            DynamicParameters parameter = new DynamicParameters();
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(@"SELECT ProvinceId,ProvinceCode,ProvinceName,ProvinceHeatLevel,ProvinceEasyBackGoods,CreateTime,UpdateTime,Editer,ProvinceNegotiatedPrice,ProvinceNegotiatedPricePartLoad FROM Province");
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                sql.AppendLine("WHERE ProvinceCode=?ProvinceCode");
                parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            }
            sql.AppendFormat(@"LIMIT {0} , {1};
                        SELECT COUNT(1) FROM Province ", startIndex, size);
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                sql.AppendLine("WHERE ProvinceCode=?ProvinceCode");
            }

            var result = this.databaseContext.Slave.QueryMultiple<ProvinceEntity, int>(sql.ToString(), parameter);

            dataCount = result.Item2;

            return result.Item1;
        }
        public bool CheckProvinceExistsById(long provinceId)
        {
            string sql = @"SELECT 1 FROM Province WHERE ProvinceId=?ProvinceId LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceId", provinceId, DbType.Int64);
            var result = this.databaseContext.Slave.ExecuteScalar(sql, parameter);
            return result != null;
        }

        public bool CheckProvinceExistsByName(string provinceName)
        {
            string sql = @"SELECT 1 FROM Province WHERE ProvinceName=?ProvinceName LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceName", provinceName, DbType.AnsiString, size: 50);
            var result = this.databaseContext.Slave.ExecuteScalar(sql, parameter);
            return result != null;
        }

        public bool CheckProvinceExistsByCode(string provinceCode)
        {
            string sql = @"SELECT 1 FROM Province WHERE ProvinceCode=?ProvinceCode";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            var result = this.databaseContext.Slave.ExecuteScalar(sql, parameter);
            return result != null;
        }

        public bool CheckProvinceIdInUsed(string provinceCode)
        {
            string sql = @"SELECT 1 FROM City WHERE ProvinceCode=?ProvinceCode LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            var result = this.databaseContext.Slave.ExecuteScalar(sql, parameter);
            return result != null;
        }

        public async Task<int> UpdateProvinceNegotiatedPrice(string provinceCode, FreightBusinessType businessType, bool isNegotiatedPrice)
        {
            LastLoadOrCheckTime = null;

            DynamicParameters parameter = new DynamicParameters();
            string sql = string.Empty;
            if (businessType == FreightBusinessType.FullLoad)
            {
                sql = @"UPDATE Province SET 
                            ProvinceNegotiatedPrice=?ProvinceNegotiatedPrice
                        WHERE ProvinceCode=?ProvinceCode";
                parameter.Add("?ProvinceNegotiatedPrice", isNegotiatedPrice, DbType.Boolean);
            }
            else
            {
                sql = @"UPDATE Province SET 
                            ProvinceNegotiatedPricePartLoad=?ProvinceNegotiatedPricePartLoad
                        WHERE ProvinceCode=?ProvinceCode";
                parameter.Add("?ProvinceNegotiatedPricePartLoad", isNegotiatedPrice, DbType.Boolean);
            }

            parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameter);
        }

        /// <summary>
        /// 修改坐标
        /// </summary>
        /// <param name="entity"></param>
        public bool UpdateCoordinate(string tableName, string provinceCode, double longitude, double latitude)
        {
            LastLoadOrCheckTime = null;

            string sql = @"UPDATE " + tableName + @" SET 
                                Longitude=?Longitude,
                                Latitude=?Latitude 
                                WHERE " + tableName + "Id=?ProvinceCode";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            parameter.Add("?Longitude", longitude, DbType.Double);
            parameter.Add("?Latitude", latitude, DbType.Double);
            var result = this.databaseContext.Master.Execute(sql, parameter);
            return result != 0;
        }

        public async Task<ProvinceEntity> GetProvinceAsync(string provinceName, string provinceUuid, string provinceCode)
        {
            List<string> wheres = new List<string>();
            string sql = @"SELECT ProvinceId,ProvinceCode,ProvinceName,ProvinceHeatLevel,ProvinceEasyBackGoods,CreateTime,UpdateTime,Editer,ProvinceNegotiatedPrice,ProvinceNegotiatedPricePartLoad,Longitude,Latitude,ProvinceUuid,IsEnabled  
                        FROM Province ";
            DynamicParameters parameter = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(provinceName))
            {
                wheres.Add("ProvinceName LIKE ?ProvinceName");
                parameter.Add("?ProvinceName", provinceName + "%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(provinceUuid))
            {
                wheres.Add("ProvinceUuid=?ProvinceUuid");
                parameter.Add("?ProvinceUuid", provinceUuid, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode=?ProvinceCode");
                parameter.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            sql = string.Format(@"{0} {1} LIMIT 1", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<ProvinceEntity>(sql, parameter);
        }

        public async Task<int> AddProvinceAsync(ProvinceEntity entity, string batchNo)
        {
            LastLoadOrCheckTime = null;

            string sql = @"INSERT INTO Province(ProvinceCode,ProvinceName,ProvinceHeatLevel,ProvinceEasyBackGoods,Editer,ProvinceNegotiatedPrice,ProvinceNegotiatedPricePartLoad,Longitude,Latitude,ProvinceUuid,IsEnabled)
                           VALUES(?ProvinceCode,?ProvinceName,?ProvinceHeatLevel,?ProvinceEasyBackGoods,?Editer,?ProvinceNegotiatedPricePartLoad,?ProvinceNegotiatedPricePartLoad,?Longitude,?Latitude,UUID(),?IsEnabled);";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ProvinceName", entity.ProvinceName, DbType.AnsiString, size: 50);
            parameter.Add("?ProvinceHeatLevel", entity.ProvinceHeatLevel, DbType.Int32, size: 11);
            parameter.Add("?ProvinceEasyBackGoods", entity.ProvinceEasyBackGoods.ToString(), DbType.AnsiString);
            parameter.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);
            parameter.Add("?ProvinceNegotiatedPrice", entity.ProvinceNegotiatedPrice, DbType.Boolean);
            parameter.Add("?ProvinceNegotiatedPricePartLoad", entity.ProvinceNegotiatedPricePartLoad, DbType.Boolean);
            parameter.Add("?Longitude", entity.Longitude.Value, DbType.Double);
            parameter.Add("?Latitude", entity.Latitude.Value, DbType.Double);
            parameter.Add("?ProvinceCode", entity.ProvinceCode, DbType.AnsiString);
            parameter.Add("?IsEnabled", entity.IsEnabled, DbType.Boolean);
            var result = await this.databaseContext.Master.ExecuteAsync(sql, parameter, databaseContext.Transaction);
            if (result > 0)
            {
                sql = this.GetUpdateCityChange(entity, batchNo, out DynamicParameters updateParameter);
                return await this.databaseContext.Master.ExecuteAsync(sql, updateParameter, databaseContext.Transaction);
            }
            return result;
        }

        private string GetUpdateCityChange(ProvinceEntity entity, string batchNo, out DynamicParameters updateParameter)
        {
            string sql_cityChange = string.Empty;
            updateParameter = new DynamicParameters();
            sql_cityChange = SqlCommands.GetInsertSql("CityChange", new string[] { "BatchNo", "NewProvinceName", "NewProvinceCode", "IsNew", "Type", "Content" });
            updateParameter.Add("IsNew", true, DbType.Boolean);
            updateParameter.Add("Content", "写入新省份信息 " + entity.ProvinceCode + ":" + entity.ProvinceName, DbType.AnsiString);
            updateParameter.Add("?BatchNo", batchNo, DbType.AnsiString);
            updateParameter.Add("?NewProvinceName", entity.ProvinceName, DbType.AnsiString);
            updateParameter.Add("?NewProvinceCode", entity.ProvinceCode, DbType.AnsiString);
            updateParameter.Add("?Type", "Province", DbType.AnsiString);
            return sql_cityChange;
        }

        public async Task<int> UpdateProvinceAsync(ProvinceEntity entity, string batchNo, ProvinceEntity provinceResult, bool? isMatch)
        {
            LastLoadOrCheckTime = null;

            string sql = @"UPDATE Province SET 
                                UpdateTime=NOW(),
                                Editer=?Editer,
                                Longitude=?Longitude,
                                Latitude=?Latitude,
                                IsEnabled=?IsEnabled 
                                WHERE ProvinceUuid=?ProvinceUuid";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?Editer", entity.Editer, DbType.AnsiString, size: 20);
            parameter.Add("?Longitude", entity.Longitude.Value, DbType.Double);
            parameter.Add("?Latitude", entity.Latitude.Value, DbType.Double);
            parameter.Add("?IsEnabled", entity.IsEnabled.Value, DbType.Boolean);
            parameter.Add("?ProvinceUuid", entity.ProvinceUuid, DbType.AnsiString);
            var result = await this.databaseContext.Master.ExecuteAsync(sql, parameter, databaseContext.Transaction);
            if (result > 0 && isMatch.HasValue && !isMatch.Value)
            {
                sql = this.GetUpdateCityChange(entity, batchNo, provinceResult, out DynamicParameters updateParameter);
                return await this.databaseContext.Master.ExecuteAsync(sql, updateParameter, databaseContext.Transaction);
            }
            return result;
        }

        private string GetUpdateCityChange(ProvinceEntity entity, string batchNo, ProvinceEntity provinceResult, out DynamicParameters updateParameter)
        {
            string sql_cityChange = string.Empty;
            updateParameter = new DynamicParameters();

            sql_cityChange = SqlCommands.GetInsertSql("CityChange",
                new string[] { "BatchNo", "OldProvinceName", "NewProvinceName", "OldProvinceCode", "NewProvinceCode",
                    "OldProvinceLongitude", "NewProvinceLongitude", "OldProvinceLatitude", "NewProvinceLatitude", "IsChange", "Type", "Content" });

            updateParameter.Add("Content", "更新省信息 " + provinceResult.ProvinceCode + ":" + provinceResult.ProvinceName + " 为 " + entity.ProvinceCode + ":" + entity.ProvinceName, DbType.AnsiString);
            updateParameter.Add("?BatchNo", batchNo, DbType.AnsiString);
            updateParameter.Add("?OldProvinceName", provinceResult.ProvinceName, DbType.AnsiString);
            updateParameter.Add("?NewProvinceName", entity.ProvinceName, DbType.AnsiString);
            updateParameter.Add("?OldProvinceCode", provinceResult.ProvinceCode, DbType.AnsiString);
            updateParameter.Add("?NewProvinceCode", entity.ProvinceCode, DbType.AnsiString);
            updateParameter.Add("?OldProvinceLongitude", provinceResult.Longitude.Value, DbType.Double);
            updateParameter.Add("?NewProvinceLongitude", entity.Longitude.Value, DbType.Double);
            updateParameter.Add("?OldProvinceLatitude", provinceResult.Latitude.Value, DbType.Double);
            updateParameter.Add("?NewProvinceLatitude", entity.Latitude, DbType.Double);
            updateParameter.Add("?IsChange", true, DbType.Boolean);
            updateParameter.Add("?Type", "Province", DbType.AnsiString);
            return sql_cityChange;
        }

        public async Task<bool> UpdateIsEnabledAsync(string batchNo)
        {
            string insertSql = @"INSERT INTO CityChange(BatchNo,OldProvinceCode,OldProvinceName,OldProvinceLongitude,OldProvinceLatitude,IsDel,Type,Content) 
                                SELECT ?BatchNo,ProvinceCode,ProvinceName,Longitude,Latitude,?IsDel,?Type,?Content FROM Province WHERE IsEnabled = 0 ";
            DynamicParameters cityChangeParameters = new DynamicParameters();
            cityChangeParameters.Add("?BatchNo", batchNo, DbType.AnsiString);
            cityChangeParameters.Add("?IsDel", true, DbType.Boolean);
            cityChangeParameters.Add("?Type", "Province", DbType.AnsiString);
            cityChangeParameters.Add("?Content", "写入未匹配上省份信息", DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteAsync(insertSql, cityChangeParameters, databaseContext.Transaction) != 0;
        }
    }
}
